/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50141
Source Host           : localhost:3306
Source Database       : sport

Target Server Type    : MYSQL
Target Server Version : 50141
File Encoding         : 65001

Date: 2014-02-27 13:32:51
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `personals_title`
-- ----------------------------
DROP TABLE IF EXISTS `personals_title`;
CREATE TABLE `personals_title` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`personalid`  int(11) NOT NULL ,
`clubid`  int(11) NOT NULL ,
`leagueid`  int(11) NOT NULL ,
`categoryid`  int(11) NOT NULL ,
`seasonid`  int(11) NOT NULL ,
`y_from`  year NOT NULL ,
`y_to`  year NOT NULL ,
`created`  datetime NOT NULL ,
`modified`  datetime NOT NULL ,
`logid`  int(11) NOT NULL ,
`status`  tinyint(4) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
UNIQUE INDEX `IX_personalid_clubid_leagueid_categoryid_seasonid` USING BTREE (`personalid`, `clubid`, `leagueid`, `categoryid`, `seasonid`) 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of personals_title
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `seasons`
-- ----------------------------
DROP TABLE IF EXISTS `seasons`;
CREATE TABLE `seasons` (
`seasonid`  int(11) NOT NULL AUTO_INCREMENT ,
`y_from`  year NOT NULL ,
`y_to`  year NOT NULL ,
PRIMARY KEY (`seasonid`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2

;

-- ----------------------------
-- Records of seasons
-- ----------------------------
BEGIN;
INSERT INTO `seasons` VALUES ('1', '2013', '2014');
COMMIT;

-- ----------------------------
-- Procedure structure for `personals_title_add`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_title_add`;
CREATE PROCEDURE `personals_title_add`(ploginid int,ppersonalid int,pclubid int,pleagueid int,pcategoryid int,pseasonid int)
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(1<<1,ploginid,'personal')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN	
		CALL seasons_get_year(pseasonid,@y_from,@y_to);
		INSERT personals_title(personalid, clubid, leagueid, categoryid, seasonid, 
													 y_from, y_to, created, modified, status, 
													 logid)
		VALUE(ppersonalid, pclubid, pleagueid, categoryid, pseasonid, 
					@y_from, @y_to, NOW(), NOW(), 1,
					ploginid);
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `personals_title_delete`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_title_delete`;
CREATE PROCEDURE `personals_title_delete`(ploginid int(11),pid int(11))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rType TINYINT(4) DEFAULT 0;
	DECLARE rPerm TINYINT(4) DEFAULT 0;
	SET rType = _admin_role(ploginid);
	SET rPerm = _admin_check_permission(1<<3,ploginid,'personal');
	IF rErrCode=0 AND (rPerm=0 OR rType < 5) THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN		
		DELETE FROM personals_title WHERE id=pid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `personals_title_get`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_title_get`;
CREATE PROCEDURE `personals_title_get`(pid int(11))
    READS SQL DATA
BEGIN	
		SELECT A.*, 
					B.`name` AS club_name,
					C.`name` AS league_name, C.`level` AS league_level,
					D.firstname, D.lastname,
					E.name AS category_name,
					_season(F.y_from,F.y_to) AS season_name
		FROM personals_title A
			LEFT JOIN clubs B ON A.clubid=B.clubid
			LEFT JOIN leagues C ON A.leagueid=C.leagueid
			LEFT JOIN personals D ON A.personalid=D.personalid
			LEFT JOIN categories E ON A.categoryid=E.categoryid
			LEFT JOIN seasons F ON A.seasonid=F.seasonid
		WHERE A.id=pid;
END;

-- ----------------------------
-- Procedure structure for `personals_title_getall`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_title_getall`;
CREATE PROCEDURE `personals_title_getall`(ploginid int,pstatus tinyint,ppersonalid int,pleagueid int,pcategoryid int,pseasonid int,pstart date,pend date,poffset int,plimit smallint(6))
    READS SQL DATA
BEGIN
	#call: 1,-1,0,0,0,0,null,null,0,10
	DECLARE rFoundRows INT DEFAULT 0;
	DECLARE rType TINYINT DEFAULT 0;
	DECLARE rStatus TINYINT DEFAULT 0;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_personals_title(id bigint);	
	# permission view
	SET rType = _admin_role(ploginid);
	IF rType < 3 THEN SET rStatus = 3;
	ELSE SET rStatus = rType + 1;	
	END IF;
	SET @sqlStm = CONCAT('
												INSERT tmp_personals_title(id)
												SELECT SQL_CALC_FOUND_ROWS
														A.id
												FROM personals_title A 
												WHERE A.id IS NOT NULL',
													IF(ppersonalid > 0, CONCAT(' AND A.personalid=',ppersonalid), ''),
													IF(pleagueid > 0, CONCAT(' AND A.leagueid=',pleagueid), ''),
													IF(pcategoryid > 0, CONCAT(' AND A.categoryid=',pcategoryid), ''),
													IF(pseasonid > 0, CONCAT(' AND A.seasonid=',pseasonid), ''),
													IF(pstart <> '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(A.created,10) BETWEEN ''',pstart,''' AND ''',pend,''''),''),
													IF(pstart <> '0000-00-00' AND pend = '0000-00-00', CONCAT(' AND LEFT(A.created,10)=''',pstart,''''),''),
													IF(pstart = '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(A.created,10)=''',pend,''''),''),
												' ORDER BY A.id DESC
												LIMIT ',poffset,',',plimit
											);

	PREPARE stmt FROM @sqlStm;
	EXECUTE stmt;

	SET rFoundRows = FOUND_ROWS();
	
	SELECT SQL_SMALL_RESULT rFoundRows AS foundRows, 
		A.*,
		B.`name` AS club_name,
		C.`name` AS league_name, C.`level` AS league_level,
		D.firstname, D.lastname,
		E.name AS category_name,
		_season(F.y_from,F.y_to) AS season_name
	FROM tmp_personals_title `AT` 
		JOIN personals_title A ON `AT`.id=A.id
		LEFT JOIN clubs B ON A.clubid=B.clubid
		LEFT JOIN leagues C ON A.leagueid=C.leagueid
		LEFT JOIN personals D ON A.personalid=D.personalid
		LEFT JOIN categories E ON A.categoryid=E.categoryid
		LEFT JOIN seasons F ON A.seasonid=F.seasonid;

	DROP TEMPORARY TABLE IF EXISTS tmp_personals_title;	
END;

-- ----------------------------
-- Procedure structure for `personals_title_update`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_title_update`;
CREATE PROCEDURE `personals_title_update`(ploginid int,pid int,ppersonalid int,pclubid int,pleagueid int,pcategoryid int,pseasonid int)
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(1<<2,ploginid,'personal')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		CALL seasons_get_year(pseasonid,@y_from,@y_to);
		UPDATE personals_title
		SET personalid=ppersonalid,
				clubid=pclubid,
				leagueid=pleagueid,
				categoryid=pcategoryid,
				seasonid=pseasonid,
				y_from=@y_from,
				y_to=@y_to,
				modified=NOW(),
				logid=ploginid
		WHERE id=pid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `personals_title_update_status`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_title_update_status`;
CREATE PROCEDURE `personals_title_update_status`(ploginid int(11),pid int(11),pstatus tinyint(4),pperm tinyint(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(pperm,ploginid,'personal')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 THEN		
		UPDATE personals_title SET `status`=pstatus WHERE id=pid;	
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `seasons_get_year`
-- ----------------------------
DROP PROCEDURE IF EXISTS `seasons_get_year`;
CREATE PROCEDURE `seasons_get_year`(pseasonid int,OUT py_from year,OUT py_to year)
    READS SQL DATA
BEGIN
	SELECT y_from,y_to INTO py_from, py_to
	FROM seasons
	WHERE seasonid=pseasonid;

END;

-- ----------------------------
-- Function structure for `_season`
-- ----------------------------
DROP FUNCTION IF EXISTS `_season`;
CREATE FUNCTION `_season`(py_from year,py_to year) RETURNS varchar(255) CHARSET utf8
    READS SQL DATA
BEGIN
	RETURN CONCAT(py_from,'/',py_to);
END;

-- ----------------------------
-- Auto increment value for `personals_title`
-- ----------------------------
ALTER TABLE `personals_title` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `seasons`
-- ----------------------------
ALTER TABLE `seasons` AUTO_INCREMENT=2;
